﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class CardsDBAccess

    {
        /// <summary>
        /// Insert Cards
        /// </summary>
        /// <param name="objCards"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public bool AddNewCards(Cards objCards, String CONNECTION_STRING)
        { 
            SqlParameter [] parameter = new SqlParameter[]
            {
               // new SqlParameter("@CardID", objCards.CardID),
                new SqlParameter("@Created", objCards.Created),
                new SqlParameter("@Updated", objCards.Updated),
                new SqlParameter("@Isactive", objCards.Isactive),
                new SqlParameter("@AccountNumber", objCards.AccountNumber),
                new SqlParameter("@CardNumber", objCards.CardNumber),
                new SqlParameter("@UserID", objCards.UserID),
                new SqlParameter("@Opendate", objCards.Opendate),
                new SqlParameter("@Balance", objCards.Balance),
                new SqlParameter("@Currency", objCards.Currency),
                new SqlParameter("@CardTypeID", objCards.CardTypeID),
                new SqlParameter("@LocationID", objCards.LocationID),
                new SqlParameter("@Status", objCards.Status),
                new SqlParameter("@Descriptions", objCards.Descriptions),
                new SqlParameter("@AccountHolders", objCards.AccountHolders),
                new SqlParameter("@ExpirationDate", objCards.ExpirationDate),
            };
            return SqlDBHelper.ExecuteNonQuery("AddNewCards", CommandType.StoredProcedure, CONNECTION_STRING, parameter);
        }

        /// <summary>
        /// Update Cards
        /// </summary>
        /// <param name="objCards"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public bool UpdateCards(Cards objCards, String CONNECTION_STRING) {

            SqlParameter[] parameter = new SqlParameter[]
            {
                //new SqlParameter("@CardID", objCards.CardID),
                new SqlParameter("@Created", objCards.Created),
                new SqlParameter("@Updated", objCards.Updated),
                new SqlParameter("@Isactive", objCards.Isactive),
                new SqlParameter("@AccountNumber", objCards.AccountNumber),
                new SqlParameter("@UserID", objCards.UserID),
                new SqlParameter("@Opendate", objCards.Opendate),
                new SqlParameter("@Balance", objCards.Balance),
                new SqlParameter("@Currency", objCards.Currency),
                new SqlParameter("@CardTypeID", objCards.CardTypeID),
                new SqlParameter("@LocationID", objCards.LocationID),
                new SqlParameter("@Status", objCards.Status),
                new SqlParameter("@Descriptions", objCards.Descriptions),
                new SqlParameter("@AccountHolders", objCards.AccountHolders),
                new SqlParameter("@ExpirationDate", objCards.ExpirationDate),
            };
            return SqlDBHelper.ExecuteNonQuery("UpdateCards", CommandType.StoredProcedure, CONNECTION_STRING, parameter);
        }

        /// <summary>
        /// Delete Cards
        /// </summary>
        /// <param name="CardID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool DeleteCards(int CardID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@CardID", CardID)
            };

            return SqlDBHelper.ExecuteNonQuery("DeleteCards", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Get Cards Details
        /// </summary>
        /// <param name="CardID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public Cards GetCardsDetails(int CardsID, String CONNECTION_STRING) {

            Cards objCards = null;
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter ("@CardID", CardsID),
            };
            //Lets get the list of all Cards in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetCardsDetails", CommandType.StoredProcedure, CONNECTION_STRING, parameter)) 
            {
                //check if any record exist or not
                if (table.Rows.Count == 1) {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Cards
                    objCards = new Cards();

                    //Now lets populate Cards details into the list of Cards
                    objCards.CardID = Convert.ToInt32(row["CardID"]);
                    objCards.Created = row["Created"].ToString();
                    objCards.Updated = row["Updated"].ToString();
                    objCards.Isactive = row["Isactive"].ToString();
                    objCards.AccountNumber = row["AccountNumber"].ToString();
                    objCards.UserID = Convert.ToInt32(row["UserID"]);
                    objCards.Opendate = row["Opendate"].ToString();
                    objCards.Balance = Convert.ToDouble(row["Balance"]);
                    objCards.Currency = row["Currency"].ToString();
                    objCards.CardTypeID = Convert.ToInt32(row["CardTypeID"]);
                    objCards.LocationID = Convert.ToInt32(row["LocationID"]);
                    objCards.Status = row["Status"].ToString();
                    objCards.Descriptions = row["Descriptions"].ToString();
                    objCards.AccountHolders = row["AccountHolders"].ToString();
                    objCards.ExpirationDate = row["ExpirationDate"].ToString();

                }
            }
            return objCards;
        }

        /// <summary>
        /// Get Cards Details
        /// </summary>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public Cards GetCardsDetailsTransfer(String AccountNumber, String CONNECTION_STRING)
        {

            Cards objCards = null;
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter ("@AccountNumber", AccountNumber),
            };
            //Lets get the list of all Cards in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetCardsDetailsTransfer", CommandType.StoredProcedure, CONNECTION_STRING, parameter))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Cards
                    objCards = new Cards();

                    //Now lets populate Cards details into the list of Cards
                    objCards.CardID = Convert.ToInt32(row["CardID"]);
                    objCards.Created = row["created"].ToString();
                    objCards.Updated = row["updated"].ToString();
                    objCards.Isactive = row["isactive"].ToString();
                    objCards.CardNumber = row["cardnumber"].ToString();
                    objCards.AccountNumber = row["AccountNumber"].ToString();
                    objCards.CardTypeID = Convert.ToInt32(row["CardTypeID"]);
                    objCards.UserID = Convert.ToInt32(row["UserID"]);
                    objCards.Opendate = row["opendate"].ToString();
                    objCards.Balance = Convert.ToDouble(row["balance"]);
                    objCards.Currency = row["Currency"].ToString();
                    objCards.LocationID = Convert.ToInt32(row["LocationID"]);
                    objCards.Status = row["status"].ToString();
                    objCards.Descriptions = row["Descriptions"].ToString();
                    objCards.AccountHolders = row["AccountHolders"].ToString();
                    objCards.ExpirationDate = row["ExpirationDate"].ToString();

                }
            }
            return objCards;
        }

        /// <summary>
        /// Get Cards DataTable
        /// </summary>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public DataTable GetCardsTableReceived(String AccountNumber, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber",AccountNumber),
            };
            //Lets get the list of all Non Term Current in a datataable
            DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetCardsTableReceived", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
            return table;
        }

        /// <summary>
        /// Get GetInfoReceived
        /// </summary>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public DataTable GetInfoReceived(String AccountNumber, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber",AccountNumber),
            };
            //Lets get the list of all Non Term Current in a datataable
            DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetInfoReceived", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
            return table;
        }


        /// <summary>
        /// Get Cards List
        /// </summary>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public List<Cards> GetCardsList(String CONNECTION_STRING)
        {

            List<Cards> listCards = null;

            //Lets get the list of all Cards in a datataable
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetCardsList", CommandType.StoredProcedure, CONNECTION_STRING))
            {
                //check if any record exist or not
                if(table.Rows.Count >0)
                {
                    //Lets go ahead and create the list of Cards
                    listCards = new List<Cards>();

                    //Now lets populate Cardsdetails into the list of Cards
                    foreach (DataRow row in table.Rows)
                    {
                        Cards objCards = new Cards();
                        objCards.CardID = Convert.ToInt32(row["CardID"]);
                        objCards.Created = row["Created"].ToString();
                        objCards.Updated = row["Updated"].ToString();
                        objCards.Isactive = row["Isactive"].ToString();
                        objCards.AccountNumber = row["AccountNumber"].ToString();
                        objCards.UserID = Convert.ToInt32(row["UserID"]);
                        objCards.Opendate = row["Opendate"].ToString();
                        objCards.Balance = Convert.ToDouble(row["Balance"]);
                        objCards.Currency = row["Currency"].ToString();
                        objCards.CardTypeID = Convert.ToInt32(row["CardTypeID"]);
                        objCards.LocationID = Convert.ToInt32(row["LocationID"]);
                        objCards.Status = row["Status"].ToString();
                        objCards.Descriptions = row["Descriptions"].ToString();
                        objCards.AccountHolders = row["AccountHolders"].ToString();
                        objCards.ExpirationDate = row["ExpirationDate"].ToString();
                        listCards.Add(objCards);
                    }
                }  
            }
            return listCards;
        }
        /// <summary>
        /// Get Cards DataTable
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public DataTable GetCardsTable(int UserID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserID",UserID),
            };
            //Lets get the list of all Non Term Current in a datataable
            DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetCardsTable", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
            return table;
        }
        /// <summary>
        /// Update Balance
        /// </summary>
        /// <param name="Balance"></param>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public bool UpdateBalanceReceived(String AccountNumber, Double Balance, String CONNECTION_STRING)
        {

            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber",AccountNumber),
                new SqlParameter("@Balance", Balance),
            };
            return SqlDBHelper.ExecuteNonQuery("UpdateBalanceReceived", CommandType.StoredProcedure, CONNECTION_STRING, parameter);
        }

        /// <summary>
        /// Update Balance
        /// </summary>
        /// <param name="objCards"></param>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public bool UpdateBalanceTransfer(String AccountNumber, Double Balance, String CONNECTION_STRING)
        {

            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber",AccountNumber),
                new SqlParameter("@Balance", Balance),
            };
            return SqlDBHelper.ExecuteNonQuery("UpdateBalanceTransfer", CommandType.StoredProcedure, CONNECTION_STRING, parameter);
        }

    }
}
